package com.kin.shoppinglist;

import android.content.ContentProvider;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;

public class ListProvider extends ContentProvider {
	
	private final String DATABASE_NAME = "shoppingdb.db";
	
	private DatabaseHelper helper;
	
	private final static String TABLE_PRODUCT_GROUP = "ProductGroup";
	private final static String TABLE_PRODUCTS = "Products";
	private final static String TABLE_SHOPPING_LIST = "ShoppingList";
	
	private final static String COLUMN_NAME = "name";
	private final static String COLUMN_PRODUCT_GROUP = "productGroup";
	private final static String COLUMN_PRODUCT_ID = "productID";
	private final static String COLUMN_GROUP_ID = "groupID";
	private final static String COLUMN_AMOUNT = "amount";
	private final static String COLUMN_CHECKED = "checked";
	private final static String COLUMN_CONTACT = "contact";
	
	private final static String CREATE_PRODUCT_GROUP = "Create Table ProductGroup 	( " +
	    "groupID int PRIMARY KEY AUTOINCREMENT, " +
		"name VarChar(20))";

	private final static String CREATE_PRODUCTS = "Create Table Products ( " +
		"productID int PRIMARY KEY AUTOINCREMENT, " +
		"name VarChar(20), " +
		"groupID int, " +
		"Foreign Key (groupID) references ProductGroup )";

	private final static String CREATE_SHOPPING_LIST = "Create Table ShoppingList ( " +
		"itemID int PRIMARY KEY AUTOINCREMENT, " +
		"productID int, " +
		"amount int, " + 
		"checked bool, " +
		"contact INT, " +
		"Foreign key (productID) references Products )";

	@Override
	public int delete(Uri arg0, String arg1, String[] arg2) {
		// delete from the table that appears in the URI's path
		String table = arg0.getPath();
		SQLiteDatabase db = helper.getWritableDatabase();		
		return db.delete(table, arg1, arg2);
	}

	@Override
	public String getType(Uri uri) {
		return "text/plain";
	}

	@Override
	public Uri insert(Uri uri, ContentValues values) {
		String table = uri.getPath();
		SQLiteDatabase db = helper.getWritableDatabase();
		long rowId = db.insert(table, null, values);
		Uri newRow = Uri.fromParts("table", table, String.valueOf(rowId));
		return newRow;
	}

	@Override
	public boolean onCreate() {
		helper = new DatabaseHelper(getContext(), DATABASE_NAME, null, 1);		
		return false;
	}

	@Override
	public Cursor query(Uri uri, String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {
		SQLiteDatabase db = helper.getReadableDatabase();
	
		// return the query results assuming that the table name appears in the URI's path
		String table = uri.getPath();
		return db.query(table, projection, selection, selectionArgs, null, null, sortOrder);		
	}

	@Override
	public int update(Uri uri, ContentValues values, String selection,
			String[] selectionArgs) {
		
		// update the table that appears in the URI's path
		String table = uri.getPath();
		SQLiteDatabase db = helper.getWritableDatabase();		
		return db.update(table, values, selection, selectionArgs);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper 
	{
		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {			
			db.execSQL(CREATE_PRODUCT_GROUP);
			db.execSQL(CREATE_PRODUCTS);
			db.execSQL(CREATE_SHOPPING_LIST);			
			
			// load the existing products list here
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {			
			db.execSQL("DROP TABLE " + TABLE_PRODUCT_GROUP);
			db.execSQL("DROP TABLE " + TABLE_PRODUCTS);
			db.execSQL("DROP TABLE " + TABLE_SHOPPING_LIST);
			onCreate(db);
		}
	}

}
